﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using VPBS.Common;

namespace VPBS.SqlClient.DAL
{
    public class TnTempEE
    {
        private string sConnectString;
        private SqlConnection my_conn = null;

        private void closeConnect()
        {
            this.my_conn.Close();
            this.my_conn = null;
        }
        private void openConnect()
        {
            this.sConnectString = ConfigurationSettings.AppSettings["strSQL"];
            this.my_conn = new SqlConnection(sConnectString);
            this.my_conn.Open();
        }
        private bool executeSQL(string strSQL)
        {
            openConnect();
            SqlCommand sqlcm = new SqlCommand(strSQL, this.my_conn);
            sqlcm.CommandType = CommandType.Text;
            try
            {
                if (sqlcm.ExecuteNonQuery() > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                closeConnect();
            }
        }
        public DataSet returnDataSet(string strSQL)
        {
            openConnect();
            SqlDataAdapter sqlDa = new SqlDataAdapter(strSQL, this.my_conn);
            DataSet ds = new DataSet();
            try
            {
                sqlDa.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
                return null;
            }
            finally
            {
                closeConnect();
            }
        }

        public bool Update(string ACCT_ID, string INSERTDATE, string TRADERID, string CREDITLIMIT, string BUYCREDIT, string CUSTTYPE, string CREDITTYPE, string T_BUY, string T_SELL, string SELLSHORTCREDIT, string PC, string ASSET, string LIABILITY, string EQUITY, string CASH_BALANCE, string LMV, string COLLAT, string DEBT, string SMV, string MR, string BUYMR, string SELLMR, string EE, string PP, string CALL_MARGIN, string CALL_FORCE, string BRK_CALL_LMV, string BRK_CALL_SMV, string BRK_SELL_LMV, string BRK_SELL_SMV, string ACTION, string BUYUNMATCH, string SELLUNMATCH, string AR, string AP, string AR_T1, string AP_T1, string AR_T2, string AP_T2, string WITHDRAWAL, string LMV_HAIRCUT, string EQUITY_HAIRCUT, string EE_1, string EE_50, string EE_60, string EE_70, string EE_MTM, string EE_MTM50, string EE_MTM60, string EE_MTM70, string UPDATEFLAG, string DELFLAG, string ADV_WITHDRAW, string RATIO)
        {
            string sSQL = "update tmpeeAccountTab_CB set ";
            sSQL += "INSERTDATE_USER=INSERTDATE, ";
            sSQL += "INSERTDATE=N'" + INSERTDATE + "', ";
            sSQL += "TRADERID=N'" + TRADERID + "', ";
            sSQL += "CREDITLIMIT=" + CREDITLIMIT + ", ";
            sSQL += "BUYCREDIT=" + BUYCREDIT + ", ";
            sSQL += "CUSTTYPE='" + CUSTTYPE + "', ";
            sSQL += "CREDITTYPE='" + CREDITTYPE + "', ";
            sSQL += "T_BUY=" + T_BUY + ", ";
            sSQL += "T_SELL=" + T_SELL + ", ";
            sSQL += "SELLSHORTCREDIT=" + SELLSHORTCREDIT + ", ";
            sSQL += "PC='" + PC + "', ";
            sSQL += "ASSET=" + ASSET + ", ";
            sSQL += "LIABILITY=" + LIABILITY + ", ";
            sSQL += "EQUITY=" + EQUITY + ", ";
            sSQL += "CASH_BALANCE=" + CASH_BALANCE + ", ";
            sSQL += "LMV=" + LMV + ", ";
            sSQL += "COLLAT=" + COLLAT + ", ";
            sSQL += "DEBT=" + DEBT + ", ";
            sSQL += "SMV=" + SMV + ", ";
            sSQL += "MR=" + MR + ", ";
            sSQL += "BUYMR=" + BUYMR + ", ";
            sSQL += "SELLMR=" + SELLMR + ", ";
            sSQL += "EE=" + EE + ", ";
            sSQL += "PP=" + PP + ", ";
            sSQL += "CALL_MARGIN=" + CALL_MARGIN + ", ";
            sSQL += "CALL_FORCE=" + CALL_FORCE + ", ";
            sSQL += "BRK_CALL_LMV=" + BRK_CALL_LMV + ", ";
            sSQL += "BRK_CALL_SMV=" + BRK_CALL_SMV + ", ";
            sSQL += "BRK_SELL_LMV=" + BRK_SELL_LMV + ", ";
            sSQL += "BRK_SELL_SMV=" + BRK_SELL_SMV + ", ";
            sSQL += "ACTION='" + ACTION + "', ";
            sSQL += "BUYUNMATCH=" + BUYUNMATCH + ", ";
            sSQL += "SELLUNMATCH=" + SELLUNMATCH + ", ";
            sSQL += "AR=" + AR + ", ";
            sSQL += "AP=" + AP + ", ";
            sSQL += "AR_T1=" + AR_T1 + ", ";
            sSQL += "AP_T1=" + AP_T1 + ", ";
            sSQL += "AR_T2=" + AR_T2 + ", ";
            sSQL += "AP_T2=" + AP_T2 + ", ";
            sSQL += "WITHDRAWAL=" + WITHDRAWAL + ", ";
            sSQL += "LMV_HAIRCUT=" + LMV_HAIRCUT + ", ";
            sSQL += "EQUITY_HAIRCUT=" + EQUITY_HAIRCUT + ", ";
            sSQL += "EE_1=" + EE_1 + ", ";
            sSQL += "EE_50=" + EE_50 + ", ";
            sSQL += "EE_60=" + EE_60 + ", ";
            sSQL += "EE_70=" + EE_70 + ", ";
            sSQL += "EE_MTM=" + EE_MTM + ", ";
            sSQL += "EE_MTM50=" + EE_MTM50 + ", ";
            sSQL += "EE_MTM60=" + EE_MTM60 + ", ";
            sSQL += "EE_MTM70=" + EE_MTM70 + ", ";
            sSQL += "UPDATEFLAG='" + UPDATEFLAG + "', ";
            sSQL += "DELFLAG='" + DELFLAG + "', ";
            sSQL += "ADV_WITHDRAW=" + ADV_WITHDRAW + ", ";
            sSQL += "RATIO=" + RATIO + ", ";
            sSQL += "LAST_TEMP_EE=0 ";
            sSQL += "where ACCT_ID='" + ACCT_ID + "'";
            return executeSQL(sSQL);
        }

        public bool Add(string ACCT_ID, string INSERTDATE, string CUST_ID, string TRADERID, string CREDITLIMIT, string BUYCREDIT, string CUSTTYPE, string CREDITTYPE, string T_BUY, string T_SELL, string SELLSHORTCREDIT, string PC, string ASSET, string LIABILITY, string EQUITY, string CASH_BALANCE, string LMV, string COLLAT, string DEBT, string SMV, string MR, string BUYMR, string SELLMR, string EE, string PP, string CALL_MARGIN, string CALL_FORCE, string BRK_CALL_LMV, string BRK_CALL_SMV, string BRK_SELL_LMV, string BRK_SELL_SMV, string ACTION, string BUYUNMATCH, string SELLUNMATCH, string AR, string AP, string AR_T1, string AP_T1, string AR_T2, string AP_T2, string WITHDRAWAL, string LMV_HAIRCUT, string EQUITY_HAIRCUT, string EE_1, string EE_50, string EE_60, string EE_70, string EE_MTM, string EE_MTM50, string EE_MTM60, string EE_MTM70, string UPDATEFLAG, string DELFLAG, string ADV_WITHDRAW, string RATIO, string LAST_TEMP_EE, string TEMP_EE, string EFFDATE, string EXPIDATE, string NAMEACCT, string DES, string USERNAME)
        {
            string sSQL =
                "insert into tmpeeAccountTab_CB(ACCT_ID, INSERTDATE, CUST_ID, TRADERID, CREDITLIMIT, BUYCREDIT, CUSTTYPE, CREDITTYPE, T_BUY, T_SELL, SELLSHORTCREDIT, PC, ASSET, LIABILITY, EQUITY, CASH_BALANCE, LMV, COLLAT, DEBT, SMV, MR, BUYMR, SELLMR, EE, PP, CALL_MARGIN, CALL_FORCE, BRK_CALL_LMV, BRK_CALL_SMV, BRK_SELL_LMV, BRK_SELL_SMV, ACTION, BUYUNMATCH, SELLUNMATCH, AR, AP, AR_T1, AP_T1, AR_T2, AP_T2, WITHDRAWAL, LMV_HAIRCUT, EQUITY_HAIRCUT, EE_1, EE_50, EE_60, EE_70, EE_MTM, EE_MTM50, EE_MTM60, EE_MTM70, UPDATEFLAG, DELFLAG, ADV_WITHDRAW, RATIO, LAST_TEMP_EE, TEMP_EE, EFFDATE, EXPIDATE, NAMEACCT, DES, USERNAME) ";
            sSQL +=
                "values(N'" + ACCT_ID + "', N'" + INSERTDATE + "', N'" + CUST_ID + "', N'" + TRADERID + "', " +
                CREDITLIMIT +
                ", " + BUYCREDIT + ", '" + CUSTTYPE + "', '" + CREDITTYPE + "', " + T_BUY + ", " + T_SELL + ", " +
                SELLSHORTCREDIT + ", '" + PC + "', " + ASSET + ", " + LIABILITY + ", " + EQUITY + ", " + CASH_BALANCE +
                ", " + LMV + ", " + COLLAT + ", " + DEBT + ", " + SMV + ", " + MR + ", " + BUYMR + ", " + SELLMR + ", " +
                EE + ", " + PP + ", " + CALL_MARGIN + ", " + CALL_FORCE + ", " + BRK_CALL_LMV + ", " + BRK_CALL_SMV +
                ", " + BRK_SELL_LMV + ", " + BRK_SELL_SMV + ", '" + ACTION + "', " + BUYUNMATCH + ", " + SELLUNMATCH +
                ", " + AR + ", " + AP + ", " + AR_T1 + ", " + AP_T1 + ", " + AR_T2 + ", " + AP_T2 + ", " + WITHDRAWAL +
                ", " + LMV_HAIRCUT + ", " + EQUITY_HAIRCUT + ", " + EE_1 + ", " + EE_50 + ", " + EE_60 + ", " + EE_70 +
                ", " + EE_MTM + ", " + EE_MTM50 + ", " + EE_MTM60 + ", " + EE_MTM70 + ", '" + UPDATEFLAG + "', '" +
                DELFLAG + "', " + ADV_WITHDRAW + ", " + RATIO + ", " + LAST_TEMP_EE + ", " + TEMP_EE + ", '" + EFFDATE +
                "', N'" + EXPIDATE + "', N'" + NAMEACCT + "', N'" + DES + "', N'" + USERNAME + "')";

            return executeSQL(sSQL);
        }
    }
}
